﻿
CREATE TRIGGER [dbtTodocAdministerObjects]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
/*
CAPTURES CHANGES TO TODOC ENABLED CHANGES AND AUTO-ADMINISTRATION OF THE 
ASSOCIATED TODOC OBJECTS

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
BEGIN
	SET NOCOUNT ON;

	DECLARE @EventType NVARCHAR(128);
	DECLARE @TableSchema SYSNAME;
	DECLARE @TableName SYSNAME;

	--IF (CHARINDEX('SQL2012DEV01', @@SERVERNAME)) > 0
	--BEGIN

		SELECT	@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME'),
				@TableSchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
				@TableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

		IF NOT EXISTS (SELECT * FROM sys.extended_properties ep
			WHERE ep.major_id = OBJECT_ID(@TableSchema + '.' + @TableName)
			AND ep.class_desc = 'OBJECT_OR_COLUMN'
			AND ep.name = 'Todoc: Enabled'
			AND ep.value = 'True')
			RETURN;

		-- if the table has been altered then run it through todoc
		IF (@EventType = 'ALTER_TABLE')
		BEGIN

			EXEC [todoc].[sspAdministerObjects] 
				@TableSchema = @TableSchema,
				@TableName = @TableName;
		END

		-- if the table has been droped then drop all todoc objects
		IF (@EventType = 'DROP_TABLE')
		BEGIN
			EXEC [todoc].[sspDeleteObjectsForTable] 
				@TableSchema = @TableSchema,
				@TableName = @TableName;
		END

	--END
	--ELSE
	--BEGIN
	--	RAISERROR(N'No direct changes allowed to objects in this database.', 10, 1); 
	--	ROLLBACK;
	--END

END;